import sqlite3
from flask import Flask, request, jsonify
import time
from flask import make_response

index_text = """
    <h1>功能设计：</h1>
        <p>1、{POST} http://127.0.0.1:8888/todo/api/tasks:创建一个待办事务，需要json信息包含："description"</p>
        <p>2、{PUT} http://127.0.0.1:8888/todo/api/tasks/<id>：完成一个事务</p>
        <p>3、{GET} http://127.0.0.1:8888/todo/api/tasks：查看所有事务</p>
        <p>3.1、？split=1：分别列出所有完成和未完成的事务</p>
        <p>3.2、？before=1&complete=1&time="Time":列出该Time之前已经完成的事务</p>
        <p>3.3、？before=1&insert=1&time="Time"：列出该Time之前添加的事务</p>
"""
app = Flask(__name__)


class TODO:
    def __init__(self):
        conn = sqlite3.connect('TODO.db')
        self.conn = conn
        self.cursor = conn.cursor()
        self.cursor.execute('''CREATE TABLE IF NOT EXISTS TODO
               (ID INTEGER PRIMARY KEY  AUTOINCREMENT,
               NAME         CHAR(50)    NOT NULL,
               CREATE_TIME  DATETIME    NOT NULL,
               COMPLETE_TIME    DATETIME,
               SIGN          INT     NOT NULL);''')

    def close_sql(self):
        self.conn.close

    def insert_to_name(self, name):
        create_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
        sign = "1"
        sql_text = "INSERT INTO TODO (NAME,CREATE_TIME,SIGN) VALUES ('" + name + "','" + create_time + "'," + sign + ")"
        self.cursor.execute(sql_text)
        return sql_text

    def show(self, before, complete, time, insert):
        if time is not None and not is_vaild_date(time):
            return 0
        sql_text = "SELECT * from TODO WHERE 1 = 1 "
        if before == "1":
            status = "<="
            if complete == "1" and time is not None:
                sql_text = sql_text + "AND COMPLETE_TIME" + status + "'" + time + "'"
            if insert == "1" and time is not None:
                sql_text = sql_text + "AND CREATE_TIME" + status + "'" + time + "'"
        print(sql_text)
        return self.cursor.execute(sql_text)

    def update(self, id=0, sign=0):
        for cur in self.cursor.execute("SELECT id from TODO WHERE id = " + str(id) + \
                                       " AND COMPLETE_TIME IS NULL LIMIT 1"):
            self.cursor.execute("UPDATE TODO SET SIGN = " + str(sign))
            if id != 0:
                sql_text = "UPDATE TODO SET COMPLETE_TIME = '" + time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()) + \
                           "' WHERE ID = " + id
                return self.cursor.execute(sql_text)
        return 0

    def commit(self):
        self.conn.commit()


def date_to_timestamp(date, format_string="%Y-%m-%d %H:%M:%S"):
    time_array = time.strptime(date, format_string)
    time_stamp = int(time.mktime(time_array))
    return time_stamp


def is_vaild_date(date):
    try:
        if ":" in date:
            time.strptime(date, "%Y-%m-%d %H:%M:%S")
        else:
            time.strptime(date, "%Y-%m-%d")
        return True
    except Exception:
        return False


def get_time_desk(second):
    if second < 60:
        if second == 1:
            time_desc = "{0} second ago".format(second)
        else:
            time_desc = "{0} seconds ago".format(second)
    else:
        minute = int(second / 60)
        if minute < 60:
            if minute == 1:
                time_desc = "{0} minute ago".format(minute)
            else:
                time_desc = "{0} minutes ago".format(minute)
        else:
            hour = int(minute / 60)
            if hour < 24:
                if hour == 1:
                    time_desc = "{0} hour ago".format(hour)
                else:
                    time_desc = "{0} hours ago".format(hour)
            else:
                day = int(hour / 24)
                if day < 7:
                    if day == 1:
                        time_desc = "{0} day ago".format(day)
                    else:
                        time_desc = "{0} days ago".format(day)
                else:
                    week = int(day / 7)
                    if week < 4:
                        if week == 1:
                            time_desc = "{0} week ago".format(week)
                        else:
                            time_desc = "{0} weeks ago".format(week)
                    else:
                        month = int(week / 4)
                        if month < 12:
                            if week == 1:
                                time_desc = "{0} month ago".format(month)
                            else:
                                time_desc = "{0} months ago".format(month)
                        else:
                            year = int(month / 12)
                            if year == 1:
                                time_desc = "{0} year ago".format(year)
                            else:
                                time_desc = "{0} years ago".format(year)
    return time_desc


@app.route('/')
def index():
    return index_text


@app.route("/todo/api/tasks", methods=["POST"])
def insert():
    if not request.json or not 'description' in request.json:
        return no_access(400)
    sql = TODO()
    sql_text = sql.insert_to_name(request.json["description"])
    sql.commit()
    sql.close_sql()
    print(sql_text)
    return jsonify({"status": "200", "option": "insert", 'data': "ok"})


@app.route("/todo/api/tasks", methods=["GET"])
def show():
    sql = TODO()

    cursor = sql.show(before=request.args.get("before"), complete=request.args.get("complete"), \
                      insert=request.args.get("insert"), time=request.args.get("time"))
    if cursor == 0:
        return set_error("time formate error", "1001")
    select_list = []
    curs = []
    for c in cursor:
        curs.append(c)
    for cur in curs:
        select_json = {}
        select_json["id"] = cur[0]
        select_json["name"] = cur[1]
        select_json["create_time"] = cur[2]
        if cur[3] is None:
            select_json["complete_time"] = "未完成"
        else:
            select_json["complete_time"] = cur[3]
        if cur[4] == 1:
            select_json["sign"] = "new"
            # 修改new状态
            sql.update()
        else:
            select_json["sign"] = cur[4]
        # 如果有complete或者insert等于1，多返回一个时间描述
        if request.args.get("complete") == "1" and select_json["complete_time"] != "未完成":
            second = time.time() - date_to_timestamp(select_json["complete_time"])
            select_json["complete_time_desc"] = get_time_desk(second)
        if request.args.get("insert") == "1":
            second = time.time() - date_to_timestamp(select_json["create_time"])
            select_json["insert_time_desc"] = get_time_desk(second)
        select_list.append(select_json)
    sql.commit()
    sql.close_sql()
    # 分别列出所有完成和未完成的事务
    if request.args.get("split") == "1":
        incomplete_list = []
        complete_list = []
        for re in select_list:
            if re["complete_time"] == "未完成":
                incomplete_list.append(re)
            else:
                complete_list.append(re)
        select_json = {}
        select_json["complete"] = complete_list
        select_json["incomplete"] = incomplete_list
        print(select_json)
        return jsonify({"status": "200", "option": "select", 'data': select_json})
        # return json.dumps(select_json, ensure_ascii=False)
    print(select_list)
    return jsonify({"status": "200", "option": "select", 'data': select_list})
    # return json.dumps(select_list, ensure_ascii=False)


@app.route("/todo/api/tasks/<string:task_id>", methods=["PUT"])
def done(task_id):
    # id = request.args.get("id")
    sql = TODO()
    if sql.update(id=task_id) == 0:
        return set_error("task is completed or task_id is not exist", "1002")
    sql.commit()
    sql.close_sql()
    return jsonify({"status": "200", "option": "update", 'data': "done"})


@app.errorhandler(400)
def bad_request(error):
    return make_response(jsonify({"status": "400", 'error': '请求无效'}), 400)


@app.errorhandler(403)
def no_access(error):
    return make_response(jsonify({"status": "403", 'error': '拒绝访问'}), 403)


@app.errorhandler(404)
def not_found(error):
    return make_response(jsonify({"status": "404", 'error': '找不到您想要的资源'}), 404)


@app.errorhandler(405)
def not_allowed(error):
    return make_response(jsonify({"status": "405", 'error': '资源被禁止'}), 405)


@app.errorhandler(500)
def server_error(error):
    return make_response(jsonify({"status": "500", 'error': '服务器错误'}), 500)


def set_error(error_desc, error_code):
    return jsonify({"status": error_code, 'error': error_desc})


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=8888, debug=True)